package com.fs;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import com.db.DB;

public class FileInvoices {

	private String DB_DATE_FORMAT = "yyyy-MM-dd";
	private String FILE_DATE_FORMAT = "dd/MM/yyyy";	
	private File file;
	private ArrayList<FinalResult> finalResults = new ArrayList<FinalResult>();

	ArrayList<String> bans;
	
	public FileInvoices(File file, ArrayList<String> bans) {
		this.file = file;
		this.bans = bans;
	}
	
	public ArrayList<FinalResult> start() {
		try {
			ArrayList<String[]> invoices = getInvoicesFromFile(file);
			for (String[] invoice:invoices) {
				Result[] res = check(invoice);
				finalResults.add(new FinalResult(invoice, res));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return finalResults;
	}
	
	private Result[] check(String[] invoice) throws Exception {
		Result[] result = new Result[29];
		result[0] = new Result(true, "Invoice_Number - OK"); //invoice_number
		result[1] = new Result(true, "BAN - OK"); //ban
		String ban = invoice[1];
		String invoice_number = invoice[0];
		
		try {
//			DB.connectToDB();
			//2. check bill_date
			try {
				Date fileDate = stringToDate(invoice[2], FILE_DATE_FORMAT);
				String dbDateStr = DB.select("bill_date", "bill", "invoice_number", invoice_number, DB.dbConnection);
				Date dbDate = stringToDate(dbDateStr, DB_DATE_FORMAT);
				Calendar c1 = Calendar.getInstance();
				c1.setTime(dbDate);
				c1.add(Calendar.DATE, 1);
				dbDate = c1.getTime();
				result[2] = new Result(fileDate.equals(dbDate), "Bill date - File: " + fileDate + " &nbsp;&nbsp;&nbsp;&nbsp;DB: " + dbDate);
			} catch (Exception e) {
				result[2] = new Result(false, "Bill date Error!");
			}
			//3. check bill_due_date
			try {
				Date fileDate = stringToDate(invoice[3], FILE_DATE_FORMAT);
				String dbDateStr = DB.select("bill_due_date", "bill", "invoice_number", invoice_number, DB.dbConnection);
				Date dbDate = stringToDate(dbDateStr, DB_DATE_FORMAT);
				fileDate.equals(dbDate);
				result[3] = new Result(fileDate.equals(dbDate), "Bill due date - File: " + fileDate + " &nbsp;&nbsp;&nbsp;&nbsp;DB: " + dbDate);
			} catch (Exception e) {
				result[3] = new Result(false, "Bill due date Error!");
			}
			//4. check mailcity
			try {
				String dbMailCityStr = DB.select("Select address_name_link.BAN, ADDRESS_DATA.ADR_CITY, address_name_link.link_type" + 
						"from address_name_link  FULL JOIN ADDRESS_DATA" +
						"on address_data.address_id = address_name_link.address_id" +
						"where address_name_link.BAN in '" + ban + "' and address_name_link.Link_type = 'B'", DB.dbConnection);
				result[4] = getResult(invoice[4], dbMailCityStr, "MailCity");
			} catch (Exception e) {
				result[4] = new Result(false, "MailCity Error!");
			}
			
			//5. check MailAddress 
			try {
				String mailAddress = DB.select("Select address_data.adr_country, address_data.adr_street_name, " +
						"ADDRESS_DATA.street_type, ADDRESS_DATA.home_no, ADDRESS_DATA.adr_location, ADDRESS_DATA.apartment, " +
						"ADDRESS_DATA.adr_pob  from Address_data FULL JOIN address_name_link on " +
						"address_data.address_id = address_name_link.address_id where address_name_link.BAN" +
						" in '" + ban + "' and address_name_link.Link_type = 'B'", DB.dbConnection);
				result[5] = getResult(invoice[5], mailAddress, "MailAddres");
			} catch (Exception e) {
				result[5] = new Result(false, "MailAddress Error!");
			}
			
			//6. check MailName
			try {
				String mailName = DB.select("Select * from address_name_link FULL JOIN name_data" +
							" on name_data.name_id = address_name_link.name_id where address_name_link.BAN" +
							" in '" + ban + "' and address_name_link.Link_type = 'B'", DB.dbConnection);
				result[6] = getResult(invoice[6], mailName, "MailName");
			} catch (Exception e) {
				result[6] = new Result(false, "MailName Error!");
			}
			
			//7.MOL
			try {
				String mol = DB.select("Select * from address_name_link FULL JOIN name_data on name_data.name_id = address_name_link.name_id " +
								"where address_name_link.BAN in '" + ban + "' and address_name_link.Link_type = 'L'", DB.dbConnection);
				result[7] = getResult(invoice[7], mol, "MOL");
			} catch (Exception e) {
				result[7] = new Result(false, "MOL Error!");
			}
			
			//8.NAME
			try {
				String name = DB.select("select n.first_name, n.last_business_name, l.first_control, l.last_control " +
						"from address_name_link l FULL JOIN name_data n on n.name_id = l.name_id " +
						"where l.ban = '" + ban + "' and l.link_type='T'", DB.dbConnection);
				result[8] = getResult(invoice[8], name, "NAME");
			} catch (Exception e) {
				result[8] = new Result(false, "NAME Error!");
			}
			
			//9.BULSTAT 
			try {
				String bulstat = DB.select("select pid from BILLING_ACCOUNT where Ban = '" + ban + "'", DB.dbConnection);
				result[9] = getResult(invoice[9], bulstat, "BULSTAT");
			} catch (Exception e) {
				result[9] = new Result(false, "BULSTAT Error!");
			}
			
			//10.COMPNO 
			try {
				String compNo = DB.select("select BL_VAT_REG_NUM from BILLING_ACCOUNT where Ban = '" + ban + "'", DB.dbConnection);
				result[10] = getResult(invoice[10], compNo, "COMPNO");
			} catch (Exception e) {
				result[10] = new Result(false, "COMPNO Error!");
			}
			
		  //11.CITY  
			try {
				String compNo = DB.select("Select address_data.adr_city from  address_data " +
								"FULL JOIN address_name_link on address_name_link.address_id = address_data.address_id " + 
								"where address_name_link.BAN in '" + ban + "' and address_name_link.Link_type = 'T'", DB.dbConnection);
				result[11] = getResult(invoice[11], compNo, "CITY");
			} catch (Exception e) {
				result[11] = new Result(false, "CITY Error!");
			}
			
		//12.CITYID  
			try {
				String cityId = DB.select("Select address_data.adr_zip from  address_data FULL JOIN address_name_link" +
								"on address_name_link.address_id = address_data.address_id " + 
								"where address_name_link.BAN in '" + ban + "' and address_name_link.Link_type = 'T'", DB.dbConnection);
				result[12] = getResult(invoice[12], cityId, "CITYID");
			} catch (Exception e) {
				result[12] = new Result(false, "CITYID Error!");
			}
			
		//13.ADDRESS  
			try {
				String address = DB.select("Select ADDRESS_DATA.adr_country, ADDRESS_DATA.adr_street_name, ADDRESS_DATA.street_type, " +
						"ADDRESS_DATA.home_no, ADDRESS_DATA.adr_location, ADDRESS_DATA.apartment, ADDRESS_DATA.adr_pob from  address_data " +
						"FULL JOIN address_name_link on address_name_link.address_id = address_data.address_id " + 
						"where address_name_link.BAN in '" + ban + "' and address_name_link.Link_type = 'T'", DB.dbConnection);
				result[13] = getResult(invoice[13], address, "ADDRESS");
			} catch (Exception e) {
				result[13] = new Result(false, "ADDRESS Error!");
			}
			
		//14.COMPNY 
			try {
				String dbValue = DB.select("account_category from BILLING_ACCOUNT where BAN in '" + ban + "'", DB.dbConnection);
				result[14] = getResult(invoice[14], dbValue, "COMPNY");
			} catch (Exception e) {
				result[14] = new Result(false, "COMPNY Error!");
			}
			
		//15.MONTHLYFEE  
			try {
				String dbValue = DB.select("Select CURR_RC_CHRG_AMT, CURR_OC_CHRG_AMT from Bill where BAN in '" + ban + "'", DB.dbConnection);
				result[15] = getResult(invoice[15], dbValue, "MONTHLYFEE");
			} catch (Exception e) {
				result[15] = new Result(false, "MONTHLYFEE Error!");
			}
			
		//16.VOICE  
			try {
				String dbValue = DB.select("select actv_code, ftr_revenue_code, actv_amt from charge where BAN in '" + ban + "' " +
						"and FTR_REVENUE_CODE = 'U' and ACTV_CODE = 'CHG';", DB.dbConnection);
				result[16] = getResult(invoice[16], dbValue, "VOICE");
			} catch (Exception e) {
				result[16] = new Result(false, "VOICE Error!");
			}
			
		//17.CHARGE 
			try {
				String dbValue = DB.select("", DB.dbConnection);//TODO
				result[17] = getResult(invoice[17], dbValue, "CHARGE");
			} catch (Exception e) {
				result[17] = new Result(false, "CHARGE Error!");
			}
			
			//18.DDS
			try {
				String dbValue = DB.select("select Tax_total_inv_amount from BILL   where ban in '" + ban + "'", DB.dbConnection);
				result[18] = getResult(invoice[18], dbValue, "DDS");
			} catch (Exception e) {
				result[18] = new Result(false, "DDS Error!");
			}
			
		//19.TOTAL 
			try {
				String dbValue = DB.select("select total_due_amt from BILL   where ban in '" + ban + "'", DB.dbConnection);
				result[19] = getResult(invoice[19], dbValue, "TOTAL");
			} catch (Exception e) {
				result[19] = new Result(false, "TOTAL Error!");
			}
			
		//20.MAILCITYID 
			try {
				String dbValue = DB.select("Select ADDRESS_DATA.adr_zip from  address_data FULL JOIN address_name_link " +
						"on address_name_link.address_id = address_data.address_id " + 
						"where address_name_link.BAN in '" + ban + "' and address_name_link.Link_type = 'B'", DB.dbConnection);
				result[20] = getResult(invoice[20], dbValue, "MAILCITYID");
			} catch (Exception e) {
				result[20] = new Result(false, "MAILCITYID Error!");
			}
			
		//21.DIRECTION 
			try {
				String dbValue = "0";//hardcoded value
				result[21] = getResult(invoice[21], dbValue, "DIRECTION");
			} catch (Exception e) {
				result[21] = new Result(false, "DIRECTION Error!");
			}
			
		//22.DISCOUNTS 
			try {
				String dbValue = DB.select("Select Curr_discount_amt from bill where ban in '" + ban + "'", DB.dbConnection);
				result[22] = getResult(invoice[22], dbValue, "DISCOUNTS");
			} catch (Exception e) {
				result[22] = new Result(false, "DISCOUNTS Error!");
			}
			
		//23.DEBT 
			try {
				String dbValue = DB.select("select overdue_amt from bill where ban in '" + ban + "'", DB.dbConnection);
				result[23] = getResult(invoice[23], dbValue, "DEBT");
			} catch (Exception e) {
				result[23] = new Result(false, "DEBT Error!");
			}
			
		//24.ACCOUNT_ID  
			try {
				String dbValue = DB.select("select BEN from bill where ban in '" + ban + "'", DB.dbConnection);
				result[24] = getResult(invoice[24], dbValue, "ACCOUNT_ID");
			} catch (Exception e) {
				result[24] = new Result(false, "ACCOUNT_ID Error!");
			}
			
		//25.PIN_CODE 
			try {
				String dbValue = DB.select("Select pin_code from BILLING_ENTITY where ban in '" + ban + "'", DB.dbConnection);
				result[25] = getResult(invoice[25], dbValue, "PIN_CODE");
			} catch (Exception e) {
				result[25] = new Result(false, "PIN_CODE Error!");
			}
			
		//26.BILL MEDIA 
			try {
				String dbValue = DB.select("select invoice_media from bill where ban in '" + ban + "'", DB.dbConnection);
				result[26] = getResult(invoice[26], dbValue, "BILL MEDIA");
			} catch (Exception e) {
				result[26] = new Result(false, "BILL MEDIA Error!");
			}
			
		//27.BILL LANGUAGE  
			try {
				String dbValue = DB.select("select bill_language from bill where ban in '" + ban + "'", DB.dbConnection);
				result[27] = getResult(invoice[27], dbValue, "BILL LANGUAGE");
			} catch (Exception e) {
				result[27] = new Result(false, "BILL LANGUAGE Error!");
			}
			
		//28.BILL DELIVERY  
			try {
				String dbValue = DB.select("select prt_category from bill where ban in '" + ban + "'", DB.dbConnection);
				result[28] = getResult(invoice[28], dbValue, "BILL DELIVERY");
			} catch (Exception e) {
				result[28] = new Result(false, "BILL DELIVERY Error!");
			}
		}catch (Exception e) {
				Result[] res = {new Result(false, "No DB Connection. Error: " + e.getMessage())};
				return res;
		} finally {
			DB.disconectFromDB();
		}
		return result;
	}

	private Result getResult(String fileValue, String dbValue, String fieldName) {
		fileValue = fileValue.trim();
		dbValue = dbValue.trim();
		return new Result(fileValue.equals(dbValue), fieldName + " - File: " + fileValue + " &nbsp;&nbsp;&nbsp;&nbsp;DB: " + dbValue);
	}
	
	private String[] parse(String invoice) {
		int start = 0;
		int index = invoice.indexOf("|", start);
		ArrayList<String> fields = new ArrayList<String>();
		while (index != -1) {
			String field = invoice.substring(start, index);
			fields.add(field);
			start = index + 1;
			index = invoice.indexOf("|", start);
		}
		String[] result = new String[fields.size()];
		result = fields.toArray(result);
		return result;
	}

	private ArrayList<String[]> getInvoicesFromFile(File file) throws Exception {
		if (file.isDirectory() || !file.exists()) {
			return new ArrayList<String[]>();
		}
		ArrayList<String[]> invoices = new ArrayList<String[]>();
		BufferedReader reader = new BufferedReader(new FileReader(file));
		String line = ""; 
		while ((line = reader.readLine()) != null) {
			String[] fields = parse(line);
			if (bans.size() == 0) {
				invoices.add(fields);
			} else {
				if (bans.contains(fields[1])) {
					invoices.add(fields);
				}
			}
		}
		reader.close();
		return invoices;
	}
	
	public Date stringToDate(String string, String format) {
		DateFormat df = new SimpleDateFormat(format);//"dd/MM/yyyy"
		Date today = null;
		try	{		
			today = df.parse(string);            
		} catch (ParseException e) {
//			e.printStackTrace();
		}
		return today;
	}
	
	public ArrayList<FinalResult> getFinalResults() {
		return finalResults;
	}
}
